from sqlalchemy import func, and_, alias
from sqlalchemy.orm import aliased, outerjoin

from ...db import models as db
from ...db.util import transaction
from ...exceptions import MissingParameterError
from ..base import BaseProcessor
from ..constants import RANK_RED_CARD, RANK_YELLOW_CARD, RANK_RED_YELLOW_CARD


class RankProcessor(BaseProcessor):
    """排行榜/ranks
    """
    @transaction()
    def read(self, *args, **kwargs):
        return self.query(*args, **kwargs)

    def query(self, *args, **kwargs):
        """Param

        args:( None )
        kwargs:{ leagueid }
        params: { type }

        required: type

        type:
            射手榜
            助攻榜
            红黄牌榜
        """
        # type
        type_ = kwargs.pop('type_', None)
        if type_ is None:
            return None

        rank = func.rank().over(order_by=db.Rank.score.desc()).label('rank')
        q = self.dbh.query(db.Rank)\
                    .add_columns(rank)\
                    .filter(db.Rank.type == type_)
        if kwargs:
            q = q.filter_by(**kwargs)
        return q.all()


class RankOfryCardProcessor(BaseProcessor):
    """排行榜/ranks
    """
    @transaction()
    def read(self, *args, **kwargs):
        return self.query(*args, **kwargs)

    def query(self, *args, **kwargs):
        """Param

        args:( None )
        kwargs:{ leagueid }
        """
        qred = self.dbh.query(db.Rank)\
                       .filter(db.Rank.type == RANK_RED_CARD)\
                       .filter_by(**kwargs)\
                       .subquery()
        qyellow = self.dbh.query(db.Rank)\
                          .filter(db.Rank.type == RANK_YELLOW_CARD)\
                          .filter_by(**kwargs)\
                          .subquery()

        tred = alias(qred, name='Red')
        tyellow = alias(qyellow, name='Yellow')

        # join
        rank = func.rank()\
                   .over(order_by=[tred.c.score.desc().nullslast(),
                                   tyellow.c.score.desc().nullslast()])\
                   .label('rank')

        q = self.dbh.query(tred, tyellow)\
                    .add_columns(rank)\
                    .join(tyellow,
                          and_(tred.c.leagueid == tyellow.c.leagueid,
                               tred.c.clubid == tyellow.c.clubid,
                               tred.c.userid == tyellow.c.userid),
                          full=True)

        return q.all()

    def bak(self, *args, **kwargs):
        """Param

        args:( None )
        kwargs:{ leagueid }
        """
        qred = self.dbh.query(db.Rank)\
                       .filter(db.Rank.type == RANK_RED_CARD)\
                       .filter_by(**kwargs)\
                       .subquery()
        qyellow = self.dbh.query(db.Rank)\
                          .filter(db.Rank.type == RANK_YELLOW_CARD)\
                          .filter_by(**kwargs)\
                          .subquery()

        # join
        rank = func.rank()\
                   .over(order_by=[qred.c.score.desc().nullslast(),
                                   qyellow.c.score.desc().nullslast()])\
                   .label('rank')

        q = self.dbh.query(qred, qyellow)\
                    .add_columns(rank)\
                    .join(qyellow,
                          and_(qred.c.leagueid == qyellow.c.leagueid,
                               qred.c.clubid == qyellow.c.clubid,
                               qred.c.userid == qyellow.c.userid),
                          full=True)

        return q.all()
